package com.cpi.cornpp.recurrentcharges.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.futurepurchase.exception.FuturePurchaseException;
import com.cpi.cornpp.recurrentcharges.dto.RecurrentChargesAddDto;
import com.cpi.cornpp.recurrentcharges.dto.RecurrentChargesDTO;
import com.cpi.cornpp.recurrentcharges.exception.RecurrentChargesException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class RecurrentChargesDAO extends DBDAO {
	private static final String SQL_INSERT_RECURRENT_CHARGE = "insert into MANUAL_RECURRENT_CHARGES (plant_id, month_period_version_id, cost_element_id, transport_id, currency_id, amount)  select ? , month_period_version_id , ?, ?, ?, 0 from months_periods_versions where version_id = ?  ";	
	private static final String SQL_SELECT_RECURRENT_CHARGE_BY_PLANT_TRANSPORT_COST = "select mrc.manual_recurrent_charge_id from MANUAL_RECURRENT_CHARGES mrc inner join months_periods_versions mpv on mrc.month_period_version_id = mpv.month_period_version_id where plant_id = ? and cost_element_id = ? and  transport_id = ? and version_id = ? ";
	private static final String SQL_GET_RECURRENCE_CHARGES = "select	mrc.manual_recurrent_charge_id, mrc.month_period_version_id, ce.cost_element_id, ce.short_name, c.sap_currency, p.plant_id, p.short_name, t.transport_id, t.short_name, mrc.amount from manual_recurrent_charges mrc inner join months_periods_versions mpv on mrc.month_period_version_id = mpv.month_period_version_id inner join costs_elements ce on mrc.cost_element_id = ce.cost_element_id inner join currencies c on mrc.currency_id = c.currency_id inner join plants p on mrc.plant_id = p.plant_id inner join transports t on mrc.transport_id = t.transport_id INNER JOIN MONTHS_PERIODS MP ON MPV.MONTH_PERIOD_ID = MP.MONTH_PERIOD_ID where mpv.version_id = ? order by p.short_name, t.short_name, ce.short_name, c.sap_currency, MP.START_DATE";
	private static final String SQL_UPDATE_RECURRENT_CHARGE = "update MANUAL_RECURRENT_CHARGES set amount = ? where manual_recurrent_charge_id = ? ";
	private static final String SQL_DELETE_RECURRENT_CHARGE = "DELETE FROM MANUAL_RECURRENT_CHARGES WHERE plant_id = ? and transport_id = ?  and cost_element_id = ? and month_period_version_id IN (select month_period_version_id from months_periods_versions where version_id = ?) ";
	/**
	 * Insertar un Recurrent Charge
	 * @param cpDTO
	 * @return
	 * @throws FuturePurchaseException
	 */
	public void  insert(RecurrentChargesAddDto dto) throws RecurrentChargesException{
		 
		PreparedStatement pstm = null;		
		
		try{
			
			if (dto != null){
				pstm = prepareSQL(SQL_INSERT_RECURRENT_CHARGE, dsName);				
				
				pstm.setInt(1, dto.getPlantId());
				pstm.setInt(2, dto.getCostElementId());
				pstm.setInt(3, dto.getTransportId());				
				pstm.setString(4, dto.getCurrencyId());
				pstm.setInt(5, dto.getVersionId());
				executeInsert(pstm);
			}			
			
		}catch (PoolException poolException){
			RecurrentChargesException recurrentChargesException =null;
			poolException.printStackTrace(System.out);
			recurrentChargesException= new RecurrentChargesException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw recurrentChargesException;
		} catch(SQLException sqlException){
			RecurrentChargesException recurrentChargesException=null;
			sqlException.printStackTrace(System.out);
			recurrentChargesException= new RecurrentChargesException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw recurrentChargesException;
		} finally {
			try{
				
				closeConnection(pstm.getConnection());
				
			} catch(SQLException sqle){
				
			}			
		}		
		
	}

	/**
	 * 
	 * @param dto
	 * @return
	 * @throws RecurrentChargesException
	 */
	public boolean getRecurrentChargePlantTransportCost(RecurrentChargesAddDto dto)throws RecurrentChargesException{
		boolean exist = false;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try{
			
			if (dto != null){
				pstm = prepareSQL(SQL_SELECT_RECURRENT_CHARGE_BY_PLANT_TRANSPORT_COST, dsName);
				
				pstm.setInt(1, dto.getPlantId());
				pstm.setInt(2, dto.getCostElementId());
				pstm.setInt(3, dto.getTransportId());
				pstm.setInt(4, dto.getVersionId());
				
				rs = executeQuery(pstm);
				
				if(rs.next()){
					exist = true;
				}
			}
			
		}catch (PoolException poolException){
			RecurrentChargesException recurrentChargesException=null;
			poolException.printStackTrace(System.out);
			recurrentChargesException= new RecurrentChargesException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw recurrentChargesException;
		} catch(SQLException sqlException){
			RecurrentChargesException recurrentChargesException=null;
			sqlException.printStackTrace(System.out);
			recurrentChargesException= new RecurrentChargesException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw recurrentChargesException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return exist;
	}

	public List getRecurrenceCharges(int versionId)throws RecurrentChargesException{
		List recurrentChargesDTOs = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		RecurrentChargesDTO recurrentChargesDTO = null;
		try{
			recurrentChargesDTOs = new ArrayList(0);
			preparedStatement = prepareSQL(SQL_GET_RECURRENCE_CHARGES, dsName);
			preparedStatement.setInt(1, versionId);
			resultSet = executeQuery(preparedStatement);
			while(resultSet.next()){
				recurrentChargesDTO = new RecurrentChargesDTO();
				recurrentChargesDTO.setManualRecurrentChargeId(resultSet.getInt(1));
				recurrentChargesDTO.setMonthPeriodVersionId(resultSet.getInt(2));
				recurrentChargesDTO.setCostElementId(resultSet.getInt(3));
				recurrentChargesDTO.setCostElementCode(resultSet.getString(4));
				recurrentChargesDTO.setCurrencyCode(resultSet.getString(5));
				recurrentChargesDTO.setPlantId(resultSet.getInt(6));
				recurrentChargesDTO.setPlantCode(resultSet.getString(7));
				recurrentChargesDTO.setTransportId(resultSet.getInt(8));
				recurrentChargesDTO.setTransportCode(resultSet.getString(9));
				recurrentChargesDTO.setAmount(resultSet.getDouble(10));
				recurrentChargesDTOs.add(recurrentChargesDTO);
			}
		}catch (PoolException poolException){
			RecurrentChargesException recurrentChargesException=null;
			poolException.printStackTrace(System.out);
			recurrentChargesException= new RecurrentChargesException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw recurrentChargesException;
		} catch(SQLException sqlException){
			RecurrentChargesException recurrentChargesException=null;
			sqlException.printStackTrace(System.out);
			recurrentChargesException= new RecurrentChargesException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw recurrentChargesException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return recurrentChargesDTOs;
	}

	public void update(RecurrentChargesDTO recurrentChargesDTO)throws  RecurrentChargesException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(SQL_UPDATE_RECURRENT_CHARGE, dsName);
			preparedStatement.setDouble(1, recurrentChargesDTO.getAmount());
			preparedStatement.setInt(2, recurrentChargesDTO.getManualRecurrentChargeId());
			executeInsert(preparedStatement);
		}catch (PoolException poolException){
			RecurrentChargesException recurrentChargesException=null;
			poolException.printStackTrace(System.out);
			recurrentChargesException= new RecurrentChargesException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw recurrentChargesException;
		} catch(SQLException sqlException){
			RecurrentChargesException recurrentChargesException=null;
			sqlException.printStackTrace(System.out);
			recurrentChargesException= new RecurrentChargesException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw recurrentChargesException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
	}

	public void delete(RecurrentChargesAddDto recurrentChargesAddDto)throws  RecurrentChargesException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(SQL_DELETE_RECURRENT_CHARGE, dsName);
			preparedStatement.setInt(1, recurrentChargesAddDto.getPlantId());
			preparedStatement.setInt(2, recurrentChargesAddDto.getTransportId());
			preparedStatement.setInt(3, recurrentChargesAddDto.getCostElementId());
			preparedStatement.setInt(4, recurrentChargesAddDto.getVersionId());
			executeInsert(preparedStatement);
		}catch (PoolException poolException){
			RecurrentChargesException recurrentChargesException=null;
			poolException.printStackTrace(System.out);
			recurrentChargesException= new RecurrentChargesException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			throw recurrentChargesException;
		} catch(SQLException sqlException){
			RecurrentChargesException recurrentChargesException=null;
			sqlException.printStackTrace(System.out);
			recurrentChargesException= new RecurrentChargesException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			throw recurrentChargesException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
	}
}